package browser.service;

import browser.dao.UserInfo;
import java.io.*;
import java.sql.*;
import javax.servlet.*;
import javax.servlet.http.*;
/**
 * Servlet that will display objects (procedures, functions, packages) source code and
 * execution table.
 */
public class ProcessObjects extends HttpServlet {

	public static Connection conn;
	public static Statement stmt;

	public void doGet(HttpServletRequest request,
			HttpServletResponse response) throws ServletException,
			IOException {

		doPost(request, response);
	}

	public void doPost(HttpServletRequest request,
			HttpServletResponse response) throws ServletException,
			IOException {

		response.setContentType("text/html");
		PrintWriter out = response.getWriter();

		// Check if session is expired
		if (request.getSession(false) == null
				|| (Boolean) request.getSession(false).getAttribute(
						"expired") == true) {
			out.println("Session expired");
			return;
		}

		String[] uid = request.getParameterValues("uid");
		String[] uacc = request.getParameterValues("uAccess");
		String[] objname = request.getParameterValues("objName");

		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
		} catch (ClassNotFoundException e) {
			out.println("Error loading the Driver:" + e.getMessage());
			return;
		}

		Connection conn = null;
		UserInfo user = MyUtilities.getUserInfo(uid[0]);

		if ((user.getUaccess() == null)
				|| (!uacc[0].equals(user.getUaccess()))) {
			out.print("Please login first");
			return;
		}
		try {
			conn = DriverManager.getConnection("jdbc:oracle:thin:"
					+ user.getUid() + "/" + user.getPassword()
					+ "@oraserver.cs.luc.edu:1521:cs");
		} catch (SQLException e1) {
			out.println("Error connecting to Oracle:" + e1.getMessage());
			return;
		}
		if (conn == null) {
			out.println("Null Connection");
			return;
		}

		Statement stmt = null;
		try {
			stmt = conn.createStatement();
		} catch (SQLException e) {
			out.println("createStatement " + e.getMessage());
			try {
				conn.close();
			} catch (SQLException e2) {
			}
			;
			return;
		}

		out.println("<html>");
		out.println("  <head><title>Objects");
		out.println("  </title><script type=\"text/javascript\" src=\"dojo.js\"></script>");
		out.println("<script type=\"text/javascript\" src=\"execute.js\"></script><link type=\"text/css\" rel=\"stylesheet\" href=\"main.css\"/></head>");
		out.println("  <body>");
		out.println("    <p>");

		ResultSet rset = null;
		String query = "select text from user_source where name='"
				+ objname[0] + "'" + " order by type,line";
		try {
			rset = stmt.executeQuery(query);

			while (rset.next()) {
				out.println(rset.getString("text"));
				out.println("</br>");
			}

			out.println("<hr>");

			query = "select object_type from user_objects where object_name='"
					+ objname[0] + "'";
			rset = stmt.executeQuery(query);
			rset.next();
			String object_type = rset.getString("object_type");
			if (object_type.equals("PROCEDURE")
					|| object_type.equals("FUNCTION")) {
				query = "select argument_name,data_type,data_precision,data_scale,in_out from user_arguments where object_name='"
						+ objname[0] + "' order by position";
				rset = stmt.executeQuery(query);
				out.println("<div id=\"exediv\">");
				out.println("<input type=\"hidden\" name=\"uid\" value=\""
						+ uid[0] + "\">");
				out
						.println("<input type=\"hidden\" name=\"uAccess\" value=\""
								+ uacc[0] + "\">");
				out
						.println("<input type=\"hidden\" id=\"object_name\" value=\""
								+ objname[0] + "\">");
				out.println("<div id=\"params\">");
				out.println("<table border=2 id=\"table\">");
				while (rset.next()) {
					out.println("<tr>");
					String data_type = rset.getString("data_type");
					String td = rset.getString("argument_name") + " ";
					if (data_type.equals("DATE")) {
						td += "DATE";
					} else if (data_type.equals("CHAR")) {
						td += "CHAR(" + rset.getString("data_precision")
								+ ")";
					} else if (data_type.equals("VARCHAR2")) {
						td += "VARCHAR2"
								+ (rset.getString("data_precision") == null ? ""
										: ("("
												+ rset
														.getString("data_precision") + ")"));
					} else if (data_type.equals("NUMBER")) {
						td += "NUMBER("
								+ rset.getString("data_precision")
								+ (rset.getInt("data_scale") == 0 ? ""
										: ("," + rset.getInt("data_scale")))
								+ ")";
					}
					out.println("<td>" + td + "</td>");
					if (rset.getString("in_out").equals("IN")) {
						out.println("<td><input type=\"text\" name=\""
								+ rset.getString("argument_name")
								+ "\" value=\"\" /></td>");
					} else if (rset.getString("in_out").equals("OUT")) {
						out
								.println("<td><input type=\"text\" name=\""
										+ rset.getString("argument_name")
										+ "\" value=\"\" disabled=\"true\" /></td>");
					} else if (rset.getString("in_out").equals("IN/OUT")) {
						out.println("<td><input type=\"text\" name=\""
								+ rset.getString("argument_name")
								+ "\" value=\"\" />");
						out
								.println("<input type=\"text\" name=\""
										+ rset.getString("argument_name")
										+ "\" value=\"\" disabled=\"true\" /></td>");
					}
					out.println("</tr>");
				}
				out.println("</table>");
				out.println("</div>");
				out.println("</div>");
				out.println("<input type=\"button\" id=\"execute\" value=\"EXECUTE\" /><br></br><hr></hr>");

			}

			out.println("<div id=\"note\">");
			out
					.println("<span> Due to JDBC and oracle incompatibility issue, we only accept CHAR,VARCHAR2,NUMBER,DATE data type.</span>");
			out.println("</br>");
			out
					.println("<span> Other data type i.e. boolean will cause database exexution error.</span>");
			out.println("</div>");
			out.println("    </p>");
			out.println("  </body>");
			out.println("</html>");

		} catch (SQLException e) {
			e.printStackTrace();
		}

		try {
			stmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		out.close();
	}
}
